package com.example.demo;

import com.example.demo.data.Details;
import com.example.demo.data.Goods;
import com.example.demo.data.Orders;

import java.sql.ResultSet;
import java.sql.SQLException;

public class executeSql {



    //查询用户名和密码
    public String selectUserAndPassword(String username, String password){
        ResultSet ret = null;


        String sql = "select * from users where name = \'"+username+"\' and password = \'"+password+"\';";//SQL语句
        System.out.println(sql);
        connectMysql db1 = new connectMysql(sql);//创建DBHelper对象
        String uid = null;

        try {
            ret = db1.pst.executeQuery();//执行语句，得到结果集
            while (ret.next()) {
                uid = ret.getString(1);
            }//显示数据
            ret.close();
            db1.close();//关闭连接
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return uid;
    }
    //查询用户信息
    public Details selectDetail(int userId){

        ResultSet ret = null;
        Details details = new Details();


        String sql = "select * from details where user_id ="+ userId + ";";//SQL语句
        System.out.println(sql);
        connectMysql db1 = new connectMysql(sql);//创建DBHelper对象

        try {
            ret = db1.pst.executeQuery();//执行语句，得到结果集
            while (ret.next()) {
                details.setUserId(ret.getInt(1));
                details.setUserName(ret.getString(2));
                details.setAddress(ret.getString(3));
                details.setAge(ret.getInt(4));
                int sex = ret.getInt(5);
                if(sex == 0)
                    details.setSex("女");
                else
                    details.setSex("男");
            }//显示数据
            ret.close();
            db1.close();//关闭连接
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return details;
    }



    //修改用户信息
    public void updateDetail(Details details){
        int sex = 0;
        if(details.getSex()=="男")
            sex = 1;
        String sql = "update details set address =\'"+details.getAddress()+"\',age="+details.getAge()+",sex="+sex+" where user_id="+details.getUserId()+";";//SQL语句
        System.out.println(sql);
        connectMysql db1 = new connectMysql(sql);//创建DBHelper对象
        try {
            db1.pst.executeUpdate();//执行语句，得到结果
            db1.close();//关闭连接
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    //查询全部货物信息
    public Goods selectGoods(){
        Goods good = new Goods();
        ResultSet ret = null;
        String sql = "select * from goods;";//SQL语句
        System.out.println(sql);
        connectMysql db1 = new connectMysql(sql);//创建DBHelper对象
        int[] goodId = null;
        String[] goodName = null;
        double[] goodPrice = null;
        try {
            ret = db1.pst.executeQuery();//执行语句，得到结果集
            ret.last();
            int count = ret.getRow();
            ret.beforeFirst();
            System.out.println("----------:"+count);
            goodId = new int[count];
            goodName = new String[count];
            goodPrice = new double[count];
            count = 0;
            while (ret.next()) {
                goodId[count] = ret.getInt(1);
                goodName[count] = ret.getString(2);
                goodPrice[count] = ret.getDouble(3);
                ++count;
            }//显示数据
            ret.close();
            db1.close();//关闭连接
        } catch (SQLException e) {
            e.printStackTrace();
        }
        good.setGoodId(goodId);
        good.setGoodName(goodName);
        good.setGoodPrice(goodPrice);
        return good;
    }



    //查询全部订单信息
    public Orders selectOrders(){
        Orders order = new Orders();
        ResultSet ret = null;

        String sql = "select * from data.show;";//SQL语句
        System.out.println(sql);
        connectMysql db1 = new connectMysql(sql);//创建DBHelper对象
        int[] orderId = null;
        String[] userName = null;
        String[] address = null;
        String[] goodName = null;


        try {
            ret = db1.pst.executeQuery();//执行语句，得到结果集
            ret.last();
            int count = ret.getRow();
            ret.beforeFirst();
            System.out.println("----------:"+count);
            orderId = new int[count];
            userName = new String[count];
            address = new String[count];
            goodName = new String[count];
            count = 0;
            while (ret.next()) {
                orderId[count] = ret.getInt(3);
                userName[count] = ret.getString(1);
                address[count] = ret.getString(2);
                goodName[count] = ret.getString(4);
                ++count;
            }//显示数据
            ret.close();
            db1.close();//关闭连接
        } catch (SQLException e) {
            e.printStackTrace();
        }
        order.setOrderId(orderId);
        order.setUserName(userName);
        order.setAddress(address);
        order.setGoodName(goodName);


        return order;
    }



    // 插入订单信息
    public void insertOrder(Orders order){
        String sql_user_id = "select user_id from users where name = \'"+order.getUserName()[0]+"\';";


        ResultSet ret = null;
        System.out.println(sql_user_id);
        connectMysql db1 = new connectMysql(sql_user_id);//创建DBHelper对象
        String user_id = null;

        try {
            ret = db1.pst.executeQuery();//执行语句，得到结果集
            while (ret.next()) {
                user_id = ret.getString(1);
            }//显示数据
            ret.close();
            db1.close();//关闭连接
        } catch (SQLException e) {
            e.printStackTrace();
        }

        String sql_good_id = "select good_id from goods where good_name = \'"+order.getGoodName()[0]+"\';";
        System.out.println(sql_good_id);
        connectMysql db2 = new connectMysql(sql_good_id);//创建DBHelper对象
        String good_id = null;

        try {
            ret = db2.pst.executeQuery();//执行语句，得到结果集
            while (ret.next()) {
                good_id = ret.getString(1);
            }//显示数据
            ret.close();
            db2.close();//关闭连接
        } catch (SQLException e) {
            e.printStackTrace();
        }



        String sql = "insert into orders(user_id,good_id,good_name,time) values("+user_id+","+good_id+",\'"+order.getGoodName()[0]+"\',\'20211027\');";//SQL语句
        System.out.println(sql);
        connectMysql db3 = new connectMysql(sql);//创建DBHelper对象



        try {
            db3.pst.executeUpdate();//执行语句，得到结果集
            db3.close();//关闭连接
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) {

    }
}
